﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;

using Hsmp.SocietyAdmin.Entity;



namespace Hsmp.SocietyAdmin.DataAccess
{
    public class FlatDao : IDaoInterface<Flat, FlatCriteria>
    {
        #region  Variable & constant
        #endregion  Variable & constant

        #region Constructor & Destructor
        public FlatDao() : base() { }
        #endregion Constructor & Destructor

        #region Properties
        #endregion Properties

        #region Public Methods
        #region IDaoInterface<Flat, FlatCriteria> Members

        public bool Insert(Flat FlatEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (FlatEntity.EntityState == EntityState.New)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_Flat_Insert";
                    //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                    CmdObj.CommandType = CommandType.StoredProcedure;
                    //CmdObj.CommandType = CommandType.Text;

                    //DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Input, DbType.Int32, SocietyEntity.SocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatSocietyKey", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatSocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatBlockName", ParameterDirection.Input, DbType.String, FlatEntity.FlatBlockName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumber", ParameterDirection.Input, DbType.String, FlatEntity.FlatNumber);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatFloorNumber", ParameterDirection.Input, DbType.String, FlatEntity.FlatFloorNumber);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatType", ParameterDirection.Input, DbType.String, FlatEntity.FlatType);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBedrooms", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfBedrooms);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfKitchen", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfKitchen);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfToilets", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfToilets);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfLivingroom", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfLivingroom);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfStudyroom", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfStudyroom);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBalcony", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfBalcony);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatTotalArea", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatTotalArea);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfWaterInlet", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfWaterInlet);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatOccupancy", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatOccupancy);

                    DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, FlatEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatKey", ParameterDirection.Output, DbType.Int32, DBNull.Value);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        FlatEntity.FlatKey = Convert.ToInt32(CmdObj.Parameters["@FlatKey"].Value);
                        FlatEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                        FlatEntity.Sys_DateOfModification = FlatEntity.Sys_DateOfCreation;
                        FlatEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }
                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For inserting new User entity data to database, [EntityState] attribute should be [New]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        
        public bool Update(Flat FlatEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (FlatEntity.EntityState == EntityState.Changed)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_Flat_Update";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatKey", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatSocietyKey", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatSocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatBlockName", ParameterDirection.Input, DbType.String, FlatEntity.FlatBlockName);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumber", ParameterDirection.Input, DbType.String, FlatEntity.FlatNumber);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatFloorNumber", ParameterDirection.Input, DbType.String, FlatEntity.FlatFloorNumber);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatType", ParameterDirection.Input, DbType.String, FlatEntity.FlatType);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBedrooms", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfBedrooms);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfKitchen", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfKitchen);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfToilets", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfToilets);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfLivingroom", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfLivingroom);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfStudyroom", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfStudyroom);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBalcony", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfBalcony);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatTotalArea", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatTotalArea);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfWaterInlet", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatNumberOfWaterInlet);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@FlatOccupancy", ParameterDirection.Input, DbType.Int32, FlatEntity.FlatOccupancy);
                    
                    DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, FlatEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        FlatEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                        FlatEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }

                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For Updating existing User entity data to database, [EntityState] attribute should be [Changed]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }


        public bool Delete(Flat FlatEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                FlatEntity.EntityState = EntityState.Changed;
                FlatEntity.Sys_IsDeleted = true;
                if (Update(FlatEntity))
                {
                    IsOperationSuccessful = true;
                }


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }


        public Flat GetByID(string ID)
        {
            Flat lSelectedFlat  = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Flat_GetByID";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(ID));
                //DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_IsDeleted", ParameterDirection.Input, DbType.Boolean, this.Sys_IsDeleted);
                //DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_OrganizationID", ParameterDirection.Input, DbType.Int32, this.Sys_OrganizationID);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        lSelectedFlat = new Flat(); //IMPORTANT NOTE:- Create instance only when required.
                        TranslateFlatFieldsFromReaderToEntity(lSelectedFlat, _DataReader);
                    }
                }
                else
                {
                    throw new Exception("No Flat found");
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return lSelectedFlat ;
        }


        public List<Flat> GetAll()
        {
            List<Flat> lFlatList = new List<Flat>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Flat_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        var lFlattemp = new Flat(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateFlatFieldsFromReaderToEntity(lFlattemp, _DataReader);
                        lFlatList.Add(lFlattemp);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lFlatList;
        }

        public List<Flat> GetByCriteria(FlatCriteria FlatCriteria)
        {
            var lFlatList = new List<Flat>();
            // SqlDataReader _DataReader = null;  - this is needed for the commented out code

            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_Flat_GetByCriteria";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatSocietyKey", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatSocietyKey);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatBlockName", ParameterDirection.Input, DbType.String, FlatCriteria.FlatBlockName);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumber", ParameterDirection.Input, DbType.String, FlatCriteria.FlatNumber);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatFloorNumber", ParameterDirection.Input, DbType.String, FlatCriteria.FlatFloorNumber);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatType", ParameterDirection.Input, DbType.String, FlatCriteria.FlatType);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBedrooms", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfBedrooms);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfKitchen", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfKitchen);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfToilets", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfToilets);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfLivingroom", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfLivingroom);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfStudyroom", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfStudyroom);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfBalcony", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfBalcony);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatTotalArea", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatTotalArea);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatNumberOfWaterInlet", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatNumberOfWaterInlet);
                DataAccessProviderUtility.AddParameter(CmdObj, "@FlatOccupancy", ParameterDirection.Input, DbType.Int32, FlatCriteria.FlatOccupancy);

                //DatabaseCommon.DefaultStoredProcParameterValuesForGetByCriteria(CmdObj, userCriteria); Not sure if this method will allow the SP to return the data

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();

                /*
                 * This code works *
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbConn.Open();

                SqlCommand CmdObj = new SqlCommand("Ivy_User_GetByCriteria", (SqlConnection)DbConn);

                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Parameters.Add(new SqlParameter("@UserLoginID", userCriteria.UserLoginID));

                _DataReader = CmdObj.ExecuteReader();
                 * This code works *
                */

                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        Flat lFlatTemp = new Flat();
                        this.TranslateFlatFieldsFromReaderToEntity(lFlatTemp, _DataReader);
                        lFlatList.Add(lFlatTemp);
                    }

                }
                CmdObj.Dispose();
                DbConn.Dispose();
            }
            catch (Exception ex)
            {

                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lFlatList;
        }



        #endregion
        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        private void TranslateFlatFieldsFromReaderToEntity(Flat pFlat, DbDataReader pDbDataReader)
        {
            try
            {
                pFlat.FlatKey = Convert.ToInt32(pDbDataReader["FlatKey"]);
                pFlat.FlatSocietyKey = Convert.ToInt32(pDbDataReader["FlatSocietyKey"]);
                pFlat.FlatBlockName = Convert.ToString(pDbDataReader["FlatBlockName"]);
                pFlat.FlatNumber = Convert.ToString(pDbDataReader["FlatNumber"]);
                pFlat.FlatFloorNumber = Convert.ToString(pDbDataReader["FlatFloorNumber"]);
                pFlat.FlatType = Convert.ToString(pDbDataReader["FlatType"]);
                pFlat.FlatNumberOfBedrooms = Convert.ToInt32(pDbDataReader["FlatNumberOfBedrooms"]);
                pFlat.FlatNumberOfKitchen = Convert.ToInt32(pDbDataReader["FlatNumberOfKitchen"]);
                pFlat.FlatNumberOfToilets = Convert.ToInt32(pDbDataReader["FlatNumberOfToilets"]);
                pFlat.FlatNumberOfLivingroom = Convert.ToInt32(pDbDataReader["FlatNumberOfLivingroom"]);
                pFlat.FlatNumberOfStudyroom = Convert.ToInt32(pDbDataReader["FlatNumberOfStudyroom"]);
                pFlat.FlatNumberOfBalcony = Convert.ToInt32(pDbDataReader["FlatNumberOfBalcony"]);
                pFlat.FlatTotalArea = Convert.ToInt32(pDbDataReader["FlatTotalArea"]);
                pFlat.FlatNumberOfWaterInlet = Convert.ToInt32(pDbDataReader["FlatNumberOfWaterInlet"]);
                pFlat.FlatOccupancy = Convert.ToInt32(pDbDataReader["FlatOccupancy"]);

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pFlat, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        #endregion Private Methods

    }
}

       